package com.codeduck.poi.controller.excel;

import com.codeduck.poi.pojo.ArtCrowd;
import com.codeduck.poi.util.AttachmentExportUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

/**
 * 生成Excel文件：Workbook --> Sheet --> Rows --> Cells
 *
 * @author: <a href="mailto:codeduck@163.com">Codeduck</a>
 * @date: 2023/1/28
 */
@RestController
public class WriteDataIntoExcelController {


    @GetMapping("/get/from/excel/simple")
    public void exportSimpleData(HttpServletResponse response) {
        Object[][] simpleData = getSimpleData();
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("people info");
        int rows = simpleData.length;
        int cols = simpleData[0].length;

        for (int i = 0; i < rows; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < cols; j++) {
                Cell cell = row.createCell(j);
                Object val = simpleData[i][j];
                if (val instanceof String) {
                    cell.setCellValue((String) val);
                } else if (val instanceof Integer) {
                    cell.setCellValue((Integer) val);
                } else {
                    cell.setCellValue((String) val);
                }
            }
        }
        writeToLocalDisk(workbook, "peopleInfo.xls", true);
        AttachmentExportUtil.export(workbook, "peopleInfo", response);
    }

    @GetMapping("/get/from/excel/xls")
    public void exportXlsData(HttpServletResponse response) {
        // xls: 2003版本Excel文件，对象为 HSSFWorkBook（最多存储65535行数据）
        long begin = System.currentTimeMillis();

        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("artists");

        dataPadding(sheet, this.getDataList(65534));
        AttachmentExportUtil.export(workbook, "艺术生信息", response);

        long end = System.currentTimeMillis();
        System.out.println("导出65534条数据，耗时: " + (double) (end - begin) / 1000 + "s");
    }

    @GetMapping("/get/from/excel/xlsx")
    public void exportXlsxData(HttpServletResponse response) {
        // xlsx: 2007版本Excel文件，对象为XSSFWorkbook（数据量无限制）
        long begin = System.currentTimeMillis();

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("artists");

        dataPadding(sheet, this.getDataList(99999));
        AttachmentExportUtil.export(workbook, "艺术生信息", response);

        long end = System.currentTimeMillis();
        System.out.println("导出99999条数据，耗时: " + (double) (end - begin) / 1000 + "s");
    }

    private void writeToLocalDisk(Workbook workbook, String fileName, boolean isDelete) {
        FileOutputStream outputStream = null;
        File file = new File("data/" + fileName);
        try {
            outputStream = new FileOutputStream(file);
            workbook.write(outputStream);
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (outputStream != null) {
                    outputStream.close();
                }
                if (workbook != null) {
                    workbook.close();
                }
                if (isDelete) {
                    file.delete();
                }
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }
    }

    private Object[][] getSimpleData() {
        Object[][] peoples = {
                {"姓名", "年龄", "性别"},
                {"Amy", "18", "女"},
                {"JackMa", "55", "男"},
                {"Tony", "43", "男"}};
        return peoples;
    }

    private List<ArtCrowd> getDataList(int size) {
        List<ArtCrowd> dataList = new ArrayList<>(size);
        for (int i = 0; i < size; i++) {
            ArtCrowd artCrowd = new ArtCrowd();
            if (i % 2 == 0) {
                artCrowd.setName("Tom");
                artCrowd.setAge(19);
                artCrowd.setGender("Man");
                artCrowd.setPaintingLevel("一级证书");
                artCrowd.setDance(false);
                artCrowd.setAssessmentTime(LocalDateTime.now());
                artCrowd.setHobby(null);
            } else {
                artCrowd.setName("Marry");
                artCrowd.setAge(18);
                artCrowd.setGender("Woman");
                artCrowd.setPaintingLevel("二级证书");
                artCrowd.setDance(true);
                artCrowd.setAssessmentTime(LocalDateTime.now());
                artCrowd.setHobby("钓鱼");
            }
            dataList.add(artCrowd);
        }
        return dataList;
    }

    private void dataPadding(Sheet sheet, List<ArtCrowd> dataList) {
        Row firstRow = sheet.createRow(0);
        firstRow.createCell(0).setCellValue("姓名");
        firstRow.createCell(1).setCellValue("年龄");
        firstRow.createCell(2).setCellValue("性别");
        firstRow.createCell(3).setCellValue("证书");
        firstRow.createCell(4).setCellValue("是否会跳舞");
        firstRow.createCell(5).setCellValue("考核时间");
        firstRow.createCell(6).setCellValue("爱好");

        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        for (int i = 0; i < dataList.size(); i++) {
            Row row = sheet.createRow(i + 1);
            ArtCrowd artCrowd = dataList.get(i);
            row.createCell(0).setCellValue(artCrowd.getName());
            row.createCell(1).setCellValue(artCrowd.getAge());
            row.createCell(2).setCellValue(artCrowd.getGender());
            row.createCell(3).setCellValue(artCrowd.getPaintingLevel());
            row.createCell(4).setCellValue(artCrowd.isDance());
            row.createCell(5).setCellValue(dtf.format(artCrowd.getAssessmentTime()));
            row.createCell(6).setCellValue(artCrowd.getHobby());
        }
    }
}
